from connector import DatabaseDriver


def get_all_good(driver: DatabaseDriver) -> tuple:
    results: tuple = driver.queryAll("""
        SELECT goods.good_id, goods.good_name, goods.supplier, category.cate_name, goods.nums, goods.price
        FROM goods
        JOIN category ON goods.cate_id = category.cate_id
    """)
    return results


def user_search_good(driver: DatabaseDriver, key: str) -> tuple:
    results: tuple = driver.queryAll("""
        SELECT goods.*, category.cate_name 
        FROM goods
        JOIN category ON goods.cate_id = category.cate_id
        WHERE good_name LIKE '%{}%' OR category.cate_name LIKE '%{}%'
    """.format(key, key, key))
    return results


def get_good_by_name_supplier_type(driver: DatabaseDriver, good_id: str = "", good_name: str = "", supplier: str = "",
                                   good_type: tuple = None, price: str = "") -> tuple:
    if good_id == "" and good_name == "" and supplier == "" and good_type is None and price == "":
        return get_all_good(driver)
    else:
        if good_type is not None:
            good_type = good_type[0]
        sql = """
        SELECT goods.good_id, goods.good_name, goods.supplier, category.cate_name, goods.price, goods.nums 
        FROM goods 
        JOIN category ON goods.cate_id = category.cate_id 
        WHERE """
        conditions = []
        if good_id != "":
            conditions.append("goods.good_id = {}".format(good_id))
        if good_name != "":
            conditions.append("goods.good_name LIKE '%{}%'".format(good_name))
        if supplier != "":
            conditions.append("goods.supplier LIKE '%{}%'".format(supplier))
        if good_type is not None:
            conditions.append("goods.cate_id = {}".format(good_type))
        if price != "":
            conditions.append("goods.price = {}".format(price))

        sql += " AND ".join(conditions)

        print(f"Constructed SQL: {sql}")  # Debug print statement

        results = driver.queryAll(sql=sql, log=True)

        print(f"Query Results: {results}")  # Debug print statement

        return results


def user_search_goods_by_good_id(driver: DatabaseDriver, good_id: int) -> tuple:
    results: tuple = driver.queryAll("""
        SELECT goods.*, category.cate_name 
        FROM goods
        JOIN category ON goods.cate_id = category.cate_id
        WHERE good_id = {}
    """.format(good_id))
    return results


def get_order_goods_by_user_id(driver: DatabaseDriver, user_id: int) -> list:
    return driver.queryAll("""
        SELECT orders.*, goods.good_name, goods.supplier, category.cate_name 
        FROM orders
        JOIN goods ON goods.good_id = orders.good_id
        JOIN category ON goods.cate_id = category.cate_id
        WHERE orders.user_id = {} AND order_time IS NULL
    """.format(user_id))


def user_search_goods_by_good_name(driver: DatabaseDriver, good_name: str):
    return driver.query_all("""
        SELECT goods.*, category.cate_name 
        FROM goods
        JOIN category ON goods.cate_id = category.cate_id
        WHERE good_name = "{}"
    """.format(good_name))


def update_good(driver: DatabaseDriver, good_id: int, good_name: str, good_supplier: str, cate_id: int,
                nums: int) -> int:
    return driver.update("""
        UPDATE goods 
        SET good_name = '{}', supplier = '{}', cate_id = {}, nums = {} 
        WHERE good_id = {}
    """.format(good_name, good_supplier, cate_id, nums, good_id))


def insert_good(driver: DatabaseDriver, good_id: int, good_name: str, good_supplier: str, good_type: int, good_num: int, good_price: float) -> bool:
    sql = f"""
    INSERT INTO goods (good_id, good_name, supplier, cate_id, nums, price)
    VALUES ({good_id}, '{good_name}', '{good_supplier}', {good_type}, {good_num}, {good_price})
    """
    try:
        print(f"Executing SQL: {sql}")  # 调试打印语句
        driver.insert(sql)
        return True
    except Exception as e:
        print(f"Error inserting good: {e}")
        return False

def del_good(driver: DatabaseDriver, good_id: int) -> int:
    return driver.delete("""
        DELETE FROM goods 
        WHERE good_id = {}
    """.format(good_id))
